﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBCompare
{
    public class DBServices
    {
        /// <summary>
        /// 根据表名ID获取表中的列信息
        /// </summary>
        /// <param name="TableId"></param>
        /// <returns></returns>
        public static List<ColInfo> GetColInfosByTableId(string connStr, string TableId)
        {
            DbHelper dbHelper = new DbHelper(connStr);
            var table = dbHelper.ExecuteReader($@"
select a.name as ColName,b.name as TypeName,a.[Length] from [sys].[syscolumns] a 
left join [sys].[systypes] b on a.xusertype=b.xusertype 
where a.id='{TableId}'
");
            List<ColInfo> lst = new List<ColInfo>();
            if (table?.Rows.Count > 0)
            {
                table.AsEnumerable().ToList().ForEach(x =>
                {
                    lst.Add(new ColInfo()
                    {
                        ColName = Convert.ToString(x["ColName"]),
                        TypeName = Convert.ToString(x["TypeName"]),
                        Length = Convert.ToInt32(x["Length"]),
                    });
                });
            }
            return lst;
        }

        /// <summary>
        /// 根据连接字符串获取该数据库中的所有表
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static List<TableInfo> GetTableInfoByConnStr(string connStr)
        {
            DbHelper dbHelper = new DbHelper(connStr);
            var table = dbHelper.ExecuteReader($@"
select [name] AS TableName,[id] AS TableId from sysobjects where [type] = 'u' order by [name]
");
            List<TableInfo> lst = new List<TableInfo>();
            if (table?.Rows.Count > 0)
            {
                table.AsEnumerable().ToList().ForEach(x =>
                {
                    lst.Add(new TableInfo()
                    {
                         TableId = Convert.ToString(x["TableId"]),
                         TableName = Convert.ToString(x["TableName"]),
                    });
                });
            }
            return lst;
        }
    }
}
